
Imports System
Imports System.Collections
Imports System.Data.SqlClient
Imports C3I.CodegenTools

Public Class SQLDataSchema
    Inherits DataSchema

    Dim cmd As SqlCommand, conn As SqlConnection

    ''' <summary>
    ''' Build server connection string from object properties
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub BuildConnectionString()
        If _UseAuthentication Then
            _ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & _DatabaseName & ";Data Source=" & _Server
        Else
            _ConnectionString = "Data Source=" & _Server & ";Initial Catalog=" & _DatabaseName & ";Persist Security Info=True;User ID=" & _UserID & ";Password=" & _Password
        End If

    End Sub

    Public Sub New()
        _providerName = "SQLServer"
    End Sub


    ''' <summary>
    ''' Populate table information 
    ''' </summary>
    ''' <remarks></remarks>
    Public Overrides Sub PopulateTableInfo()
        conn = New SqlConnection

        BuildConnectionString()

        conn.ConnectionString = _ConnectionString
        conn.Open()

        PopulateDataTypes()
        PopulateDescriptions()
        PopulatePrimaryKeys()
        PopulateForeignKeys()
        PopulateFields()
        PopulateIndexes()
    End Sub

    ''' <summary>
    ''' Get .NET datatypes from table
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulateDataTypes()
        'get  some data (any data) from source table

        cmd = New SqlCommand("select top 1 * from " & _TableSchema & ".[" & _TableName & "]", conn)

        Try

            reader = cmd.ExecuteReader
            _columnTypes.Clear()

            'get the .NET datatypes for each column
            For nF = 0 To reader.FieldCount - 1
                _columnTypes.Add(reader.GetName(nF), reader.GetFieldType(nF).Name)
            Next


        Catch ex As Exception
            MsgBox("Error processing table " & _TableName)
            Throw ex
        End Try

        reader.Close()
    End Sub
    ''' <summary>
    ''' Get column descriptions (if supported)
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulateDescriptions()
        'populate array with field descriptions for specified table
        cmd = New SqlCommand("SELECT objname,value FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', '" & _TableName & "', 'column', default)", conn)
        reader = cmd.ExecuteReader
        _columnDescriptions.Clear()

        Do While reader.Read
            _columnDescriptions.Add(reader.GetString(0), reader.GetString(1))
        Loop

        reader.Close()
    End Sub
    ''' <summary>
    ''' Get primary keys for specific table
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulatePrimaryKeys()
        'get Primary keys
        strSQL = "SELECT     COLS.COLUMN_NAME " & vbCrLf
        strSQL = strSQL & "FROM         INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC2 RIGHT OUTER JOIN " & vbCrLf
        strSQL = strSQL & "                      INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU ON TC2.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME RIGHT OUTER JOIN " & vbCrLf
        strSQL = strSQL & "                      INFORMATION_SCHEMA.COLUMNS AS COLS ON KCU.TABLE_NAME = COLS.TABLE_NAME AND KCU.COLUMN_NAME = COLS.COLUMN_NAME " & vbCrLf
        strSQL = strSQL & "WHERE     (COLS.TABLE_NAME = '" & _TableName & "') AND (TC2.CONSTRAINT_TYPE = 'PRIMARY KEY') "

        'populate array with primary keys for specified table
        cmd = New SqlCommand(strSQL, conn)
        reader = cmd.ExecuteReader
        _primaryKeys.Clear()
        Do While reader.Read
            _primaryKeys.Add(reader.GetString(0), reader.GetString(0))
        Loop
        reader.Close()

    End Sub
    ''' <summary>
    ''' Get foreign keys for specific table
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulateForeignKeys()

        'build SQL to get foreign keys TOP (100) PERCENT
        strSQL = "SELECT      KCU.COLUMN_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME AS FKABLE_NAME, "
        strSQL = strSQL & "                       INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME AS FTKEY,TC2.CONSTRAINT_NAME,TC2.CONSTRAINT_SCHEMA  " & vbCrLf
        strSQL = strSQL & "FROM         INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN " & vbCrLf
        strSQL = strSQL & "                      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC INNER JOIN " & vbCrLf
        strSQL = strSQL & "                      INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON  " & vbCrLf
        strSQL = strSQL & "                      RC.UNIQUE_CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME ON  " & vbCrLf
        strSQL = strSQL & "                      INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME RIGHT OUTER " & vbCrLf
        strSQL = strSQL & "                       JOIN " & vbCrLf
        strSQL = strSQL & "                      INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC2 RIGHT OUTER JOIN " & vbCrLf
        strSQL = strSQL & "                      INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU ON TC2.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME ON  " & vbCrLf
        strSQL = strSQL & "                      RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME " & vbCrLf
        strSQL = strSQL & "WHERE     (KCU.TABLE_NAME = '" & _TableName & "') AND (TC2.CONSTRAINT_TYPE = 'FOREIGN KEY') " & vbCrLf

        'populate array with foreign keys for specified table
        cmd = New SqlCommand(strSQL, conn)
        reader = cmd.ExecuteReader
        _foreignKeys.Clear()

        Do While reader.Read

            If Not ForeignKeys.ContainsKey(reader.GetString(0)) Then
                Dim foreignKeyarray(3) As String
                foreignKeyarray(1) = reader.GetString(1)
                foreignKeyarray(2) = reader.GetString(2)
                foreignKeyarray(3) = reader.GetString(3)
                foreignKeyarray(0) = reader.GetString(4)

                _foreignKeys.Add(reader.GetString(0), foreignKeyarray)

            End If
        Loop

        reader.Close()

        'populate array with foreign keys for specified table
        cmd = New SqlCommand("sp_fkeys '" & _TableName & "'", conn)
        reader = cmd.ExecuteReader
        _foreignTables.Clear()

        Do While reader.Read

            Dim foreignTables(3) As String

            foreignTables(0) = reader.GetString(5)
            foreignTables(1) = reader.GetString(6)
            foreignTables(2) = reader.GetString(7)
            foreignTables(3) = reader.GetString(11)

            _foreignTables.Add(foreignTables)
        Loop

        reader.Close()

    End Sub
    ''' <summary>
    ''' Get table columns 
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulateFields()
        Dim nF As Integer, columndictionary As Dictionary(Of String, String)

        'get INFORMATION_SCHEMA layout for table
        strSQL = "SELECT     COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE,  " & vbCrLf
        strSQL = strSQL & "        COLLATION_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMNPROPERTY(object_id('" & _TableSchema & ".' +TABLE_NAME), COLUMN_NAME, 'IsIdentity') As ISIDENTITY " & vbCrLf
        strSQL = strSQL & "FROM         INFORMATION_SCHEMA.COLUMNS AS COLS " & vbCrLf
        strSQL = strSQL & "WHERE     (TABLE_NAME = '" & _TableName & "') ORDER BY ORDINAL_POSITION" & vbCrLf

        cmd = New SqlCommand(strSQL, conn)
        reader = cmd.ExecuteReader
        _columnsList.Clear()
        Do While reader.Read
            columndictionary = New Dictionary(Of String, String)()
            'populate dictionary with column properties 
            For nF = 0 To reader.FieldCount - 1
                columndictionary.Add(reader.GetName(nF), reader.GetValue(nF).ToString)
            Next

            _columnsList.Add(columndictionary)

        Loop

        reader.Close()


    End Sub


    ''' <summary>
    ''' Get stored procedure parameters
    ''' </summary>
    ''' <remarks></remarks>
    Overrides Sub PopulateParameters()
        Dim nF As Integer, columndictionary As Dictionary(Of String, String)

        'get INFORMATION_SCHEMA layout for table
        strSQL = "SELECT     COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE,  " & vbCrLf
        strSQL = strSQL & "        COLLATION_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') As ISIDENTITY " & vbCrLf
        strSQL = strSQL & "FROM         INFORMATION_SCHEMA.COLUMNS AS COLS " & vbCrLf
        strSQL = strSQL & "WHERE     (TABLE_NAME = '" & _TableName & "') ORDER BY ORDINAL_POSITION" & vbCrLf

        cmd = New SqlCommand(strSQL, conn)
        reader = cmd.ExecuteReader
        _columnsList.Clear()
        Do While reader.Read
            columndictionary = New Dictionary(Of String, String)()
            'populate dictionary with column properties 
            For nF = 0 To reader.FieldCount - 1
                columndictionary.Add(reader.GetName(nF), reader.GetValue(nF).ToString)
            Next

            _spParameters.Add(columndictionary)

        Loop
        reader.Close()

    End Sub

    ''' <summary>
    ''' Get databases for specified server
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overrides Function GetDatabases() As Generic.List(Of String)
        Dim conn As New SqlConnection, databaseList As New Generic.List(Of String)
        Dim reader As SqlDataReader = Nothing, dcmd As SqlCommand, rowCount As Integer
        Dim ProgressBar As New frmProgressBar

        'set connection string
        conn.ConnectionString = ConnectionString


        conn.Open()
        'get all databases from server
        dcmd = New SqlCommand("EXEC sp_databases ", conn)

        reader = dcmd.ExecuteReader


        rowCount = 0
        Dim tableName As String
        databaseList.Clear()

        'get a list of databases to process 
        Do While reader.Read
            tableName = reader("database_name").ToString

            'ignore any system tables
            If tableName <> "master" And tableName <> "tempdb" And tableName <> "model" And tableName <> "temp" And tableName <> "msdb" Then
                rowCount = rowCount + 1
                databaseList.Add(tableName)
            End If
        Loop

        GetDatabases = databaseList

    End Function
    ''' <summary>
    ''' Get tables for specified database
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overrides Function GetTables() As Generic.List(Of DBObject)
        Dim conn2 As New SqlConnection()
        Dim reader As SqlDataReader, dcmd As SqlCommand, tableList As New Generic.List(Of DBObject)

        conn2.ConnectionString = ConnectionString
        conn2.Open()

        dcmd = New SqlCommand("select table_name,table_schema, TABLE_TYPE from INFORMATION_SCHEMA.TABLES", conn2)

        reader = dcmd.ExecuteReader
        tableList.Clear()


        Do While reader.Read
            Dim tableInfo As New DBObject
            tableInfo.Name = reader("TABLE_NAME").ToString
            tableInfo.Schema = reader("TABLE_SCHEMA").ToString
            tableInfo.Type = reader("TABLE_TYPE").ToString
            tableList.Add(tableInfo)
        Loop

        conn2.Close()
        GetTables = tableList

    End Function


    ''' <summary>
    ''' Get stored procedures
    ''' </summary>
    ''' <returns>List of db objects</returns>
    ''' <remarks></remarks>
    Public Overrides Function GetStoredProcedures() As Generic.List(Of DBObject)
        Dim conn2 As New SqlConnection()
        Dim reader As SqlDataReader, dcmd As SqlCommand, spList As New Generic.List(Of DBObject)

        conn2.ConnectionString = ConnectionString
        conn2.Open()

        dcmd = New SqlCommand("SELECT   name,type_desc from sys.procedures order by name", conn2)

        reader = dcmd.ExecuteReader
        spList.Clear()

        Do While reader.Read
            Dim spInfo As New DBObject
            spInfo.Name = reader("name").ToString
            'tableInfo.Schema = reader("TABLE_SCHEMA").ToString
            spInfo.Type = reader("type_desc").ToString
            spList.Add(spInfo)
        Loop

        conn2.Close()
        GetStoredProcedures = spList

    End Function

    Overrides Sub CheckIfView()
        Dim tableType As String
        'populate array with field descriptions for specified table
        cmd = New SqlCommand("select table_type from INFORMATION_SCHEMA.TABLES where  table_name='" & _TableName & "'", conn)
        tableType = cmd.ExecuteScalar()

        _isView = tableType = "VIEW"

    End Sub

    Overrides Sub PopulateIndexes()

        Dim conn2 As New SqlConnection()
        Dim reader As SqlDataReader, dcmd As SqlCommand

        conn2.ConnectionString = ConnectionString
        conn2.Open()

        dcmd = New SqlCommand("EXEC sp_helpindex '" & _TableSchema & "." & _TableName & "'", conn2)

        reader = dcmd.ExecuteReader
        _indexes.Clear()

        Do While reader.Read
            If Not _indexes.ContainsKey(reader("index_keys").ToString) Then
                Dim newIndex As New Index
                newIndex.Name = reader("index_name").ToString
                newIndex.Description = reader("index_description").ToString
                newIndex.Column = (reader("index_keys").ToString)
                _indexes.Add(reader("index_keys").ToString, newIndex)
            End If

        Loop

        conn2.Close()
    End Sub

    ''' <summary>
    ''' Get generic data type based on native database type 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overrides Function GetDataType(ByVal strNativeType As String) As String
        GetDataType = ""
        If InStr("char,nchar,varchar,text,ntext,nvarchar", strNativeType) > 0 Then
            GetDataType = "String"
        ElseIf InStr("int,bigint,smallint,tinyint,bit", strNativeType) > 0 Then
            GetDataType = "Integer"
        ElseIf InStr("money,smallmoney", strNativeType) > 0 Then
            GetDataType = "Currency"
        ElseIf InStr("real,numeric,float,decimal", strNativeType) > 0 Then
            GetDataType = "Double"
        ElseIf InStr("smalldatetime,datetime,timestamp", strNativeType) > 0 Then
            GetDataType = "Date"
        Else
            GetDataType = "Binary"
        End If

    End Function

    ''' <summary>
    ''' Get generic column data type based on native database column type 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overrides Function GetGenericColumnType(ByVal strNativeType As String) As String
        GetGenericColumnType = strNativeType
    End Function

End Class
